﻿


CREATE proc [Projects].[GetPurchaserProjectCount]
@PurchaserID	int,
@Year			int
as
begin
	set nocount on

	declare @projectNumber nchar(2),
			@weiter bit = 0,
			@ShortName nvarchar(100),
			@i int,
			@ret int = 0


	select	top 1 @ShortName = coalesce(p.ShortName,'') 
	from	Projects.datProjects p
	where	p.PurchaserID = @PurchaserID
			and year(p.FirstContactDate) = @Year
	order by p.ShortName desc

	if LEN(@ShortName) > 0
	begin
		set @i = LEN(@ShortName) - 1
		while (@weiter = 0)
		begin
			if substring(@ShortName, @i, 1) = 'p'
			begin
				set @ret = cast(substring(@ShortName, @i + 1, LEN(@ShortName) - (@i)) as int) 
				--select @i 'i', @ShortName 'ShortName', LEN(@ShortName) - @i 'Dif', @ret 'ret'
				set @weiter = 1
			end
			set @i -= 1
			if @i = 0
			begin
				set @weiter = 1
			end
		end
	end
	
	if @ret != 0
	begin
		select @ret 'ProjectsCount'
	end
	else
	begin
		select	count(*) 'ProjectsCount'
		from	Projects.datProjects p
		where	p.PurchaserID = @PurchaserID
				and year(p.FirstContactDate) = @Year
	end
end
GO


